#!/bin/bash

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
select substr(X.transaction_date,1,10),station_id,
sum(case when X.mop_name = 'EPS会员' THEN 1 ELSE 0 END)AS EPSNumber,
sum(case when X.mop_name = 'EPS会员' then item_value ELSE 0 end)as EPSMoney,
sum(case when X.mop_name = '优惠券' THEN 1 ELSE 0 END)AS couponNumber,
sum(case when X.mop_name = '优惠券' then item_value ELSE 0 end)as couponMoney,
sum(case when X.mop_name = '会员优惠券' THEN 1 ELSE 0 END)AS vipCouponNumber,
sum(case when X.mop_name = '会员优惠券' then item_value ELSE 0 end)as vipCouponNumber,
sum(case when X.mop_name = '信用卡' THEN 1 ELSE 0 END)AS creditCardNumber,
sum(case when X.mop_name = '信用卡' then item_value ELSE 0 end)as creditCardMoney,
sum(case when X.mop_name = '壳牌车队卡' THEN 1 ELSE 0 END)AS teamCardNumber,
sum(case when X.mop_name = '壳牌车队卡' then item_value ELSE 0 end)as teamCardMoney,
sum(case when X.mop_name = '微信支付' THEN 1 ELSE 0 END)AS wechatNumber,
sum(case when X.mop_name = '微信支付' then item_value ELSE 0 end)as wechatMoney,
sum(case when X.mop_name = '微车支付' THEN 1 ELSE 0 END)AS weichepayNumber,
sum(case when X.mop_name = '微车支付' then item_value ELSE 0 end)as weichepayMoney,
sum(case when X.mop_name = '支付宝' OR X.mop_name = '支付宝支付' THEN 1 ELSE 0 END)AS alipayNumber,
sum(case when X.mop_name = '支付宝' OR X.mop_name = '支付宝支付' then item_value ELSE 0 end)as alipayMoney,
sum(case when X.mop_name = '支票' THEN 1 ELSE 0 END)AS chequeNumber,
sum(case when X.mop_name = '支票' then item_value ELSE 0 end)as chequeMoney,
sum(case when X.mop_name = '滴滴支付' THEN 1 ELSE 0 END)AS didiNumber,
sum(case when X.mop_name = '滴滴支付' then item_value ELSE 0 end)as didiMoney,
sum(case when X.mop_name = '现金' THEN 1 ELSE 0 END)AS cashNumber,
sum(case when X.mop_name = '现金' then item_value ELSE 0 end)as cashMoney,
sum(case when X.mop_name = '电子支付优惠' THEN 1 ELSE 0 END)AS ePaymentNumber,
sum(case when X.mop_name = '电子支付优惠' then item_value ELSE 0 end)as ePaymentMoney,
sum(case when X.mop_name = '百度支付' THEN 1 ELSE 0 END)AS baiduNumber,
sum(case when X.mop_name = '百度支付' then item_value ELSE 0 end)as baiduNumber,
sum(case when X.mop_name = '第三方卡' THEN 1 ELSE 0 END)AS thirdPaymentNumber,
sum(case when X.mop_name = '第三方卡' then item_value ELSE 0 end)as thirdPaymentMoney,
sum(case when X.mop_name = '车到收款' THEN 1 ELSE 0 END)AS carInNumber,
sum(case when X.mop_name = '车到收款' then item_value ELSE 0 end)as carInMoney,
sum(case when X.mop_name = '银联钱包优惠券' THEN 1 ELSE 0 END)AS unionpayCouponNumber,
sum(case when X.mop_name = '银联钱包优惠券' then item_value ELSE 0 end)as unionpayCouponMoney,
sum(case when X.mop_name = '斑马支付' then 1 ELSE 0 end) as zebpayNumber,
sum(case when X.mop_name = '斑马支付' then item_value ELSE 0 end)as zebpayMoney
from
(select Z.transaction_date,Y.station_id,Y.mop_name,Y.item_value,Y.item_quantity
from ( SELECT * from transactions where substr(transaction_date,1,10)='$dt' )as Z
join transaction_items_mop as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id and Y.item_value > 0 and Y.item_value < 100000)AS X
group by substr(X.transaction_date,1,10),X.station_id;
EOF